<?php
	Connection::getConnect();
	
	if (COUNT($_POST) && $this->get('gett'))
	{
		$type = $this->get('type');
		$from = $this->get('from');
		$to = $this->get('to');
		switch ($type) {
			case 1:
			$y = date("Y", strtotime($from));
			$y2 = date("Y", strtotime($to));
        	for ($m=$y; $m<=$y2; $m++)
        	{
        		$sql = "SELECT SUM(total_sales) FROM item_sales WHERE YEAR(date)=$m";
        		Connection::setQuery($sql);
        		$months[$m] = Connection::fetch();
        	}
		?>
			<caption>Annual Sales (<?php echo $y . '-' . $y2; ?>)</caption>
                  <thead>
                      <tr>
                          <th>Month</th>
                          <th>Sales</th>
                      </tr>
                   </thead>
                   <tbody>
		<?php
			  for ($m=$y; $m<=$y2; $m++) {?>
                      <tr class="list">
                          <td><?php echo $m; ?></td>
                          <td><?php echo isset($months[$m][0])?number_format($months[$m][0], 2, '.',','):0; ?></td>
                      </tr>
		<?php } ?>
                  </tbody>
		<?php
			exit;
			case 2:
				$y = date("M d, Y", strtotime($from));
				$d = date("Y-m-d", strtotime($from));
        		$sql = "SELECT SUM(total_sales) FROM item_sales WHERE DATE_FORMAT(date,'%Y-%c-%d')='$d'";
        		Connection::setQuery($sql);
        		$daily = Connection::fetch();
		?>
		
			<caption>Daily Sales (<?php echo $y?>)</caption>
                  <thead>
                      <tr>
                          <th>Day</th>
                          <th>Sales</th>
                      </tr>
                   </thead>
                   <tbody>
                      <tr class="list">
                          <td><?php echo $y; ?></td>
                          <td><?php echo number_format($daily[0], 2, '.',','); ?></td>
                      </tr>
					</tbody>
		<?php
			exit;
			case 3:
			$y = date("Y", strtotime($from));
        	for ($m=1; $m<13; $m++)
        	{
        		$sql = "SELECT SUM(total_sales) FROM item_sales WHERE MONTH(date)=$m AND YEAR(date)=$y";
        		Connection::setQuery($sql);
        		$months[$m] = Connection::fetch();
        	}
		?>
			<caption>Monthly Sales (<?php echo $y?>)</caption>
                  <thead>
                      <tr>
                          <th>Month</th>
                          <th>Sales</th>
                      </tr>
                   </thead>
                   <tbody>
		<?php
  				$month = array(
                1 => 'January',
                2 => 'February',
                3 => 'March',
                4 => 'April',
                5 => 'May',
                6 => 'June',
                7 => 'July',
                8 => 'August',
                9 => 'September',
                10 => 'October',
                11 => 'November',
                12 => 'December');
			  for ($m=1; $m<13; $m++) {?>
                      <tr class="list">
                          <td><?php echo $month[$m]; ?></td>
                          <td><?php echo isset($months[$m][0])?number_format($months[$m][0], 2, '.',','):0; ?></td>
                      </tr>
		<?php } ?>
                  </tbody>
		<?php
			exit;
			case 4:
			$from_stamp = strtotime($from);
            $to_stamp = strtotime($to);
        	for ($m=0; $m<7; $m++)
        	{
        		$sql = "SELECT SUM(total_sales) FROM item_sales WHERE WEEKDAY(Date)=$m and unix_timestamp(Date) between $from_stamp and $to_stamp";
        		Connection::setQuery($sql);
        		$weeks[$m] = Connection::fetch();
        	}
		?>
			<caption>Weekly Sales</caption>
                  <thead>
                      <tr>
                          <th>Week</th>
                          <th>Sales</th>
                      </tr>
                   </thead>
                   <tbody>
		<?php
  				$week = array(
                0 => 'Monday',
                1 => 'Tuesday',
                2 => 'Wednesday',
                3 => 'Thursday',
                4 => 'Friday',
                5 => 'Saturday',
                6 => 'Sunday',);
			  for ($m=0; $m<7; $m++) {?>
                      <tr class="list">
                          <td><?php echo $week[$m]; ?></td>
                          <td><?php echo isset($weeks[$m][0])?number_format($weeks[$m][0], 2, '.',','):0; ?></td>
                      </tr>
		<?php } ?>
                  </tbody>
		<?php
			exit;
			case 5:
			$from_stamp = strtotime($from);
            $to_stamp = strtotime($to);
			
    			$sql = "SELECT name, balance, date, interest from item_sales_credit WHERE unix_timestamp(Date) between $from_stamp and $to_stamp";
           		Connection::setQuery($sql);
    			$array = Connection::fetchAll(MYSQL_ASSOC);
			
			?>
			<caption class="ui-text-center">Creditors</caption>
                  <thead>
                      <tr>
                          <th>Name</th>
                          <th>Balance</th>
						  <th>Lending Date</th>
						  <th>Interest</th>
                      </tr>
                   </thead>
                   <tbody>
			
			<?php foreach ($array as $result){ ?>
			<tr class="list">
			<td><?php echo $result['name']; ?> </td>
			<td><?php echo $result['balance']; ?> </td>
			<td><?php echo $result['date']; ?> </td>
			<td><?php echo $result['interest']; ?> </td>
				
			</tr>
			<?php } ?>
			</tbody>
			
			<?php
			exit;
			case 7:
			
    			$sql = "SELECT name, quantity from item_inventory join item_details using (iid) WHERE quantity < reorder";
           		Connection::setQuery($sql);
    			$array = Connection::fetchAll(MYSQL_ASSOC);
			
			?>
			<caption class="ui-text-center">Out of Stocks</caption>
                  <thead>
                      <tr>
                          <th>Name</th>
                          <th>Quantity</th>
                      </tr>
                   </thead>
                   <tbody>
			
			<?php foreach ($array as $result){ ?>
			<tr class="list">
			<td><?php echo $result['name']; ?> </td>
			<td><?php echo $result['quantity']; ?> </td>
			</tr>
			<?php } ?>
			</tbody>
				
		<?php
			exit; 	
			
			default:
			exit;
		}
    	
    	$sql = "SELECT ic.name, SUM(total_sales) FROM item_sales i JOIN item_inventory ii USING(iid) JOIN item_category ic USING(icid) GROUP BY ic.name;";
    	Connection::setQuery($sql);
    	$catsales = Connection::fetchAll(MYSQL_ASSOC);
    	
    	$sql = "SELECT ii.name, SUM(total_sales) FROM item_sales i JOIN item_details ii USING(iid) GROUP BY ii.name;";
    	Connection::setQuery($sql);
    	$itemsales = Connection::fetchAll(MYSQL_ASSOC);
	}
?>
